#!/usr/local/bin/python3

__author__ = '曹植'

import pymysql

from datetime import datetime
from log import logger


class BaseSql:
    def __init__(self, s):
        self.conn = pymysql.connect(**s)
        self.cursor = self.conn.cursor()

    def close(self):
        self.conn.close()

    def query_channel_by_mobile(self, mobile):
        """通过手机号查询出该用户axh_storehouse已经撞库的渠道"""
        uid = self.query_uid_by_mobile(mobile)
        if uid is not None:
            sql = "SELECT product_id FROM axh_storehouse WHERE uid={};"
            self.cursor.execute(sql.format(uid))
            registered_channels = self.cursor.fetchall()
            return registered_channels
        else:
            return None

    def query_uid_by_mobile(self, mobile):
        """通过手机号查询出用户的uid"""
        sql = "SELECT id FROM axh_user WHERE mobile='{}';"
        self.cursor.execute(sql.format(mobile))
        uid = self.cursor.fetchone()
        if uid is not None:
            return uid[0]
        else:
            return uid

    def query_storehouse(self, uid, product_id):
        """查询该用户是否已经在某个渠道撞库"""
        sql = "SELECT id FROM axh_storehouse WHERE uid={} AND product_id='{}';"
        self.cursor.execute(sql.format(uid, product_id))
        hit_info = self.cursor.fetchone()
        if hit_info is not None:
            return hit_info[0]
        else:
            return hit_info

    def query_usable_channel(self):
        """查询出未下架的渠道"""
        sql = "SELECT id, product_name FROM axh_product WHERE status=1 ;"
        self.cursor.execute(sql)
        channels = self.cursor.fetchall()
        return channels

    def update_storehouse(self, uid, product_id, product_name):
        """写入某个渠道该用户已经注册的信息"""
        add_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sql = "INSERT INTO axh_storehouse(uid, product_id, msg, add_time) VALUES ({},{},'{}','{}')"
        self.cursor.execute(sql.format(uid, product_id, product_name + '撞库，该用户在该渠道已注册', add_time))
        self.conn.commit()

    def query_user(self, start, end):
        sql = 'SELECT mobile FROM axh_user WHERE id BETWEEN {} AND {}'
        self.cursor.execute(sql.format(int(start), int(end)))
        user = self.cursor.fetchall()
        return user

    def query_usable_channel_api(self):
        """通过渠道号查询出撞库地址"""
        sql = "SELECT id,product_name,storehouse_link FROM axh_product  WHERE status=1 AND storehouse_link" \
              " REGEXP '^http'"
        self.cursor.execute(sql)
        channels = self.cursor.fetchall()
        if len(channels) == 0:
            return None
        else:
            return channels

    def query_mobile_md5_by_uid(self, uid):
        """通过uid查询出电话的md5值"""
        sql = 'SELECT mobile_md5 FROM axh_mobile_md WHERE uid={};'
        self.cursor.execute(sql.format(int(uid)))
        mobile_md5 = self.cursor.fetchone()
        if mobile_md5 is not None:
            return mobile_md5[0]
        else:
            return mobile_md5

    def query_mobile_by_uid(self, uid):
        """通过uid查询出电话"""
        sql = 'SELECT mobile FROM axh_user WHERE id={};'
        self.cursor.execute(sql.format(int(uid)))
        mobile = self.cursor.fetchone()
        if mobile is not None:
            return mobile[0]
        else:
            return mobile

    def query_id_card_by_mobile(self, mobile):
        """通过mobile查询出身份证信息"""
        sql = 'SELECT id_card FROM axh_user WHERE mobile="{}";'
        self.cursor.execute(sql.format(mobile))
        mobile = self.cursor.fetchone()
        if mobile is not None:
            return mobile[0]
        else:
            return mobile

    def save_joint_login_data(self, uid, channel_id):
        """
        保存联合登录数据
        :param uid:
        :param channel_id:
        :return:
        """
        sql = "INSERT INTO axh_joint_login (uid,channel_id) VALUES ({},{});"
        try:
            self.cursor.execute(sql.format(uid, channel_id))
            self.conn.commit()
        except Exception as e:
            logger.error(e)
